#include "exceldao.h"
#include "qstring.h"
#include "QAxObject"
#include "QVariant"
#include "QApplication"
#include "QFile"
#include "QDir"
#include "QList"
#include "exception"
#include "qdebug.h"
#include "qt_windows.h"
#include "QTime"


QString ExcelDao::ExportToExcel(QString save_excel_full_dir_path, QString save_excel_name, Datas_List datas_list)
{
    HRESULT r = OleInitialize(0);
    if(r != S_OK && r != S_FALSE)
    {
        qDebug(" OLD ERROR 5x",(unsigned int)r);
    }

    QString path = save_excel_full_dir_path + "/" + save_excel_name + QString("%1.xlsx").arg(QTime::currentTime().toString("hh_mm_ss_zzz"));
    ExcelDao::CreateEmptyDir(save_excel_full_dir_path);

    qDebug() << path;
    try
    {
        if(!save_excel_full_dir_path.isEmpty() && !save_excel_name.isEmpty())
        {
            // 新建excel控件
            QAxObject *excel = new QAxObject("Excel.Application");//连接Excel控件
            excel->setProperty("Visible",false);    //不显示窗体
            excel->setProperty("DisplayAlerts", false); //不显示任何警告信息。如果为true那么在关闭是会出现类似“文件已修改，是否保存”的提示

            QAxObject *work_books = excel->querySubObject("WorkBooks");//获取工作簿集合
            work_books->dynamicCall("Add");  //新建一个excel

            QAxObject *work_book = excel->querySubObject("ActiveWorkBook");
            work_book = excel->querySubObject("ActiveWorkBook");//获取当前工作簿

            QAxObject *work_sheets = work_book->querySubObject("Sheets");//获取工作表集合
            QAxObject *first_sheet = work_sheets->querySubObject("Item(int)", 1);

//            QAxObject *cell = first_sheet->querySubObject("Cells(int,int)", 1, 1);
//            cell->setProperty("Value","11eeY.Q.C");

            // 插入数据
            for(int row = 1; row <= datas_list.size(); ++row)
            {
                Datas_List_Item_List list = datas_list[row - 1];
                for(int col = 1 ; col <= list.size() ; ++col)
                {
                    QAxObject *cell = first_sheet->querySubObject("Cells(int,int)", row, col);
                    const QVariant val = list[col - 1];
                    cell->setProperty("Value",val);
                    delete cell;
                }
            }

            // 保存
            work_book->dynamicCall("SaveAs(const QString&)",QDir::toNativeSeparators(path));
            work_book->dynamicCall("Close()");//关闭工作簿
            excel->dynamicCall("Quit()");//关闭excel
            delete excel;
            excel=NULL;
        }
    }
    catch(std::exception e)
    {
        std::cout << e.what() << std::endl;
    }
    OleUninitialize();
    return path;
}

Datas_List ExcelDao::ImportFromExcel(QString excel_full_path)
{
    Datas_List *list = new Datas_List();
    HRESULT r = OleInitialize(0);
    if(r != S_OK && r != S_FALSE)
    {
        qDebug(" OLD ERROR 5x",(unsigned int)r);
    }
    try
    {
        if(!excel_full_path.isEmpty())
        {
            // 新建excel控件
            QAxObject *excel = new QAxObject();
            excel->setControl("Excel.Application");//连接Excel控件
            excel->dynamicCall("SetVisible (bool Visible)","false");//不显示窗体
            excel->setProperty("DisplayAlerts", false);//不显示任何警告信息。如果为true那么在关闭是会出现类似“文件已修改，是否保存”的提示

            QAxObject *work_books = excel->querySubObject("WorkBooks");//获取工作簿集合
            work_books->dynamicCall("Open (const QString&)", excel_full_path);
            QAxObject *work_book = excel->querySubObject("ActiveWorkBook");//获取当前工作簿
            QAxObject *work_sheets = work_book->querySubObject("Sheets");//获取工作表集合
            int sheet_count = work_sheets->property("Count").toInt();  //获取工作表数目
            if(sheet_count > 0)
            {
                QAxObject *work_sheet = work_book->querySubObject("Sheets(int)", 1);
                QAxObject *used_range = work_sheet->querySubObject("UsedRange");

/*
 *              这部分的代码有问题，没有办法获取到excel的字符串数据。 但是数值型数据就都能够获取的了
 *
//                QAxObject *rows = used_range->querySubObject("Rows");
//                int row_count = rows->property("Count").toInt();  //获取行数

//                QAxObject *cols = used_range->querySubObject("Columns");
//                int col_count = cols->property("Count").toInt();

//                // 下标从1开始
//                // 第 1 行是标题 跳过
//                for(int row = 2 ; row <= row_count ; ++row)
//                {
//                    auto info_list = new Datas_List_Item_List();
//                    for(int col = 1; col <= col_count ; ++col)
//                    {
//                        QAxObject *cell = work_sheet->querySubObject("Cells(int,int)", row, col);
//                        QVariant *val = &cell->dynamicCall("Value()");
//                        info_list->push_back(*val);
//                        delete cell;
//                    }
//                    list->push_back(*info_list);

//                    auto temp_list = *info_list;
//                    qDebug() << QString("good_id:  ") << temp_list[0] << temp_list[1] << temp_list[2] << temp_list[4] ;
//                    info_list = NULL;
//                }
*/

                if( used_range != NULL && !used_range->isNull())
                {
                      QVariant var = used_range-> dynamicCall("Value");//读取区域内所有值
                      QList<QVariant> rows_map = var.toList();
                      for(int row = 1 ; row < rows_map.count() ; ++row)
                      {
                          auto rows_list = rows_map[row].toList();
                          auto info_list = new Datas_List_Item_List();
                          for(int col = 0; col < rows_list.count();++col)
                          {
                              info_list->push_back(rows_list[col]);
                          }
                          list->push_back(*info_list);
                          //auto temp_list = *info_list;
                          //qDebug() << QString("good_id:  ") << temp_list[0] << temp_list[1] << temp_list[2] << temp_list[4] ;
                          info_list = NULL;
                      }
                }

              work_book->dynamicCall("Close(Boolean)", false);  //关闭文件
              excel->dynamicCall("Quit(void)");  //退出
            }
        }

    }catch(std::exception e)
    {
        std::cout<<e.what()<<std::endl;
    }
    OleUninitialize();
    return *list;
}

void ExcelDao::CreateEmptyDir(QString dir_path)
{
    //ExcelDao::DelDir(dir_path);
    QDir dir(dir_path);
    if(!dir.exists()) dir.mkdir(dir_path);
}

void ExcelDao::DelDir(QString dir_path)
{
    // 如果目录存在就删除目录下的文件，留空目录
    // 如果目录不存在就创建
    QDir dir(dir_path);
    if(dir.exists())
    {
        dir.setFilter(QDir::AllEntries | QDir::NoDotAndDotDot);
        QFileInfoList fileList = dir.entryInfoList(); // 获取所有的文件信息
        foreach (QFileInfo file, fileList)
        {
            if (file.isFile())
            {
                file.dir().remove(file.fileName()); // 删除文件
            }
            else
            {
               ExcelDao::DelDir( file.absoluteFilePath());// 递归删除
            }
        }
        dir.rmpath(dir.absolutePath());
    }
}
